import re
import os
import chardet  # 检测文件编码
import sqlparse  # 解析SQL
import tkinter as tk  # Tkinter库，用于创建GUI
from tkinter import filedialog, messagebox  # 文件对话框和消息框

class SQLValidatorApp:
    def __init__(self, master):
        self.master = master
        self.master.title("SQL Schema 校验工具 by lxx")

        # 文件选择框
        self.file_label = tk.Label(master, text="选择要验证的SQL文件:")
        self.file_label.grid(row=0, column=0, padx=10, pady=5)
        self.file_path_var = tk.StringVar()
        self.file_path_entry = tk.Entry(master, textvariable=self.file_path_var, width=50)
        self.file_path_entry.grid(row=0, column=1, padx=10, pady=5)
        self.browse_button = tk.Button(master, text="浏览", command=self.browse_files)
        self.browse_button.grid(row=0, column=2, padx=10, pady=5)

        # Schema 输入框
        self.schema_label = tk.Label(master, text="输入要校验的 Schema:")
        self.schema_label.grid(row=1, column=0, padx=10, pady=5)
        self.schema_var = tk.StringVar()
        self.schema_entry = tk.Entry(master, textvariable=self.schema_var, width=50)
        self.schema_entry.grid(row=1, column=1, padx=10, pady=5)

        # 表空间校验复选框
        self.tablespace_var = tk.BooleanVar()
        self.tablespace_check = tk.Checkbutton(master, text="校验表空间", variable=self.tablespace_var)
        self.tablespace_check.grid(row=2, column=0, padx=10, pady=5)

        # 开始校验按钮
        self.validate_button = tk.Button(master, text="开始校验", command=self.validate_sql)
        self.validate_button.grid(row=2, column=1, padx=10, pady=5)

        # 错误文件路径结果输出窗口
        self.result_text = tk.Text(master, width=60, height=10, wrap=tk.WORD)
        self.result_text.grid(row=3, column=0, columnspan=3, padx=10, pady=(5, 20))

    def browse_files(self):
        # 打开文件对话框以选择SQL文件，支持多选
        file_paths = filedialog.askopenfilenames(filetypes=[("SQL Files", "*.sql")])
        if file_paths:
            self.file_path_var.set(";".join(file_paths))

    def get_file_encoding(self, file_path):
        # 检测文件编码
        with open(file_path, 'rb') as f:
            result = chardet.detect(f.read())
        return result['encoding']

    def remove_comments(self, sql):
        # 移除SQL语句中的注释
        sql = re.sub(r'--.*?$', '', sql, flags=re.MULTILINE)  # 移除行注释
        sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)  # 移除块注释
        return sql

    def read_sql_file(self, file_path):
        # 读取SQL文件，返回原始行和去除注释后的行
        encoding = self.get_file_encoding(file_path)
        original_lines = []
        clean_lines = []
        try:
            with open(file_path, 'r', encoding=encoding) as file:
                for line in file:
                    original_lines.append(line)
                    clean_lines.append(self.remove_comments(line))
        except (UnicodeDecodeError, IOError) as e:
            messagebox.showerror("错误", f"读取文件时出错: {e}")
        return original_lines, clean_lines

    def validate_sql(self):
        # 主校验函数
        file_paths = self.file_path_var.get().split(";")
        schema = self.schema_var.get()
        check_tablespace = self.tablespace_var.get()

        if not file_paths:
            messagebox.showerror("错误", "请选择要验证的文件")
            return

        if not schema:
            messagebox.showerror("错误", "请输入要校验的 Schema")
            return

        for file_path in file_paths:
            if not os.path.isfile(file_path):
                self.result_text.insert(tk.END, f"文件 {file_path} 不存在\n")
                continue

            original_lines, clean_lines = self.read_sql_file(file_path)

            if not clean_lines:
                self.result_text.insert(tk.END, f"文件 {file_path} 读取失败\n")
                continue

            # 将去除注释后的SQL拼接成一个字符串
            raw_sql = ''.join(clean_lines)
            parsed = sqlparse.parse(raw_sql)

            # 匹配视图、表、索引和表空间的正则表达式模式
            schema_view_table_pattern = re.compile(r'\b(view|from|table|into|update)\s+([^\s\(\);]+)', re.IGNORECASE)
            create_table_pattern = re.compile(r'\bcreate\s+table\b', re.IGNORECASE)
            create_index_pattern = re.compile(r'\bcreate\s+(unique\s+)?index\b', re.IGNORECASE)
            tablespace_pattern = re.compile(r'\btablespace\b', re.IGNORECASE)

            problem_sqls = []

            current_pos = 0
            for statement in parsed:
                sql = str(statement).strip()
                # 计算SQL语句在原始文件中的开始位置
                start_idx = raw_sql.find(sql, current_pos)
                if start_idx == -1:
                    continue

                # 更新current_pos，避免重复匹配
                current_pos = start_idx + len(sql)

                # 计算SQL语句在原始文件中的行号
                num_lines_before = raw_sql[:start_idx].count('\n')
                start_line = num_lines_before + 1

                matches = schema_view_table_pattern.findall(sql)
                missing_or_mismatched = False
                for match in matches:
                    schema_name = match[1].split('.')[0]
                    if schema_name.lower() != schema.lower():
                        missing_or_mismatched = True
                        break

                if missing_or_mismatched:
                    problem_sqls.append(f'-- 错误: schema 错误或缺失，位于第 {start_line} 行\n{sql}\n')
                elif check_tablespace and (create_table_pattern.search(sql) or create_index_pattern.search(sql)) and not tablespace_pattern.search(sql):
                    problem_sqls.append(f'-- 错误: 缺少表空间语句，位于第 {start_line} 行\n{sql}\n')

            if problem_sqls:
                error_file_path = file_path.replace('.sql', '_error.sql')
                try:
                    with open(error_file_path, 'w', encoding='UTF-8') as error_file:
                        for statement in problem_sqls:
                            error_file.write(statement + '\n')
                    self.result_text.insert(tk.END, f'源文件:\n{file_path}\n问题SQL已保存到:\n{error_file_path}\n')
                except IOError as e:
                    messagebox.showerror("错误", f"保存错误文件时出错: {e}")
            else:
                self.result_text.insert(tk.END, f'源文件:\n{file_path}\n验证通过\n')
            self.result_text.insert(tk.END, "="*60 + "\n")

if __name__ == "__main__":
    root = tk.Tk()
    app = SQLValidatorApp(root)
    root.mainloop()
